package cn.lm.demo02.utils;

import cn.lm.demo02.domain.Book;

import java.sql.*;
import java.text.MessageFormat;

public class TBookHelper {
    // 数据库连接参数
    private static final String url = "jdbc:mysql://127.0.0.1:3306/limindb" +
            "?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=" +
            "Asia/Shanghai&allowPublicKeyRetrieval=true";

    // 数据库登录用户名
    private static final String username = "root";

    // 数据库登录密码
    private static final String password = "root";

    private Connection conn = null;
    private Statement stmt = null;

    public TBookHelper() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url,username,password);
    }

    /**
     * 添加图书
     * @param book
     * @return
     * @throws SQLException
     */
    public boolean add(Book book) throws SQLException {
        String insertSql = "Insert Into t_book(id,name,author,price) value(?,?,?,?)";

        PreparedStatement pstmt = conn.prepareStatement(insertSql);
        pstmt.setInt(1,book.getId());
        pstmt.setString(2,book.getName());
        pstmt.setString(3,book.getAuthor());
        pstmt.setDouble(4,book.getPrice());

        int rows = pstmt.executeUpdate();

        System.out.println("添加图书成功,影响行数:"+rows);

        return true;
    }

    /**
     * 删除图书
     * @param id
     * @return
     */
    public boolean delete(int id) throws SQLException {
        String deleteSql = "delete from t_book where id=?";

        PreparedStatement pstmt = conn.prepareStatement(deleteSql);
        pstmt.setInt(1,id);

        int rows = pstmt.executeUpdate();

        System.out.println("删除图书成功,影响行数:"+rows);

        return true;
    }

    /**
     * 更新图书
     * @param book
     * @return
     */
    public boolean update(Book book,int editId) throws SQLException {

        String updateSql = "Update t_book Set id=?,name=?,author=?,price=? " +
                                "where id=?";

        PreparedStatement pstmt = conn.prepareStatement(updateSql);
        pstmt.setInt(1,book.getId());
        pstmt.setString(2,book.getName());
        pstmt.setString(3,book.getAuthor());
        pstmt.setDouble(4,book.getPrice());
        pstmt.setInt(5,editId);

        int rows = pstmt.executeUpdate();

        System.out.println("更新图书成功,影响行数:"+rows);

        return true;
    }

    /**
     * 查询图书
     */
    public void query() throws SQLException {
        String querySql = "Select id,name,author,price from t_book";

        PreparedStatement pstms = conn.prepareStatement(querySql);

        ResultSet rs = pstms.executeQuery();

        while(rs.next()){
            String info = MessageFormat.format("图书Id:{0},图书名称:{1},图书作者:{2},图书价格:{3}",
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("author"),
                    rs.getDouble("price"));
            System.out.println(info);
        }
    }

    @Override
    protected void finalize() throws Throwable {
        if(conn!=null) conn.close();
    }
}
